package Drivers;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import Drivers.*;
import DBPack.*;
public class FerryDriver {

	public static Ferry[] getAllFerryList() {
		try{
			String query="SELECT * FROM ferry " ;
			PreparedStatement ps=(PreparedStatement) DBStatement.getMainConnection().prepareStatement(query);
			ResultSet result = ps.executeQuery();
			if(result.next())
				return convertFerryToArray(result);
		}  catch(SQLException e) {
		} catch (Exception e) {
		}
		return null;
	}
	public static Ferry[] getFerryList(String input) {
		try{
			String query="SELECT * FROM ferry where name like ? order by name asc";
			PreparedStatement ps=(PreparedStatement) DBStatement.getMainConnection().prepareStatement(query);
			ps.setString(1, input+"%");
			ResultSet result = ps.executeQuery();
			if(result.next())
				return convertFerryToArray(result);
		}  catch(SQLException e) {
		} catch (Exception e) {
		}
		return null;
	}
	public static Ferry getFerryByName(String ferryName) {
		try{
			String query="SELECT * FROM ferry where `name`= ?" ;
			PreparedStatement ps=(PreparedStatement) DBStatement.getMainConnection().prepareStatement(query);
			ps.setString(1, ferryName);
			ResultSet result = ps.executeQuery();
			if(result.next())
				return new Ferry(result.getInt("id"), result.getString("name"),result.getString("departure"),result.getString("arrival"));
		}  catch(SQLException e) {
		} catch (Exception e) {
		}
		return null;
	}
	public static Ferry getFerryById(String ferryId) {
		try{
			String query="SELECT * FROM ferry where `id`= ?" ;
			PreparedStatement ps=(PreparedStatement) DBStatement.getMainConnection().prepareStatement(query);
			ps.setString(1, ferryId);
			ResultSet result = ps.executeQuery();
			if(result.next())
				return new Ferry(result.getInt("id"), result.getString("name"),result.getString("departure"),result.getString("arrival"));
		}  catch(SQLException e) {
		} catch (Exception e) {
		}
		return null;
	}
	public static FerryTime getFerryTimeByName(String ferryName) {
		try{
			String query="SELECT * FROM ferry_time where `ferry_name`= ?" ;
			PreparedStatement ps=(PreparedStatement) DBStatement.getMainConnection().prepareStatement(query);
			ps.setString(1, ferryName);
			ResultSet result = ps.executeQuery();
			if(result.next())
				return new FerryTime(result.getInt("time_id"), result.getInt("ferry_id"), result.getString("name"),result.getTime("time"),result.getString("direction"),result.getString("day_code"));
		}  catch(SQLException e) {
		} catch (Exception e) {
		}
		return null;
		
		
	}
	public static FerryTime[] getFerryTimeList(int ferryId) {
		try{
			String query="SELECT * FROM ferry_time where `ferry_id`= ?" ;
			PreparedStatement ps=(PreparedStatement) DBStatement.getMainConnection().prepareStatement(query);
			ps.setInt(1, ferryId);
			ResultSet result = ps.executeQuery();
			if(result.next())
				return convertFerryTimeToArray(result);
		}  catch(SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
		
		
	}
	public static FerryTime[] getFerryTimeList(int ferryId,String direction) {
		try{
			String query="SELECT * FROM ferry_time where `ferry_id`= ? and `direction`= ?" ;
			PreparedStatement ps=(PreparedStatement) DBStatement.getMainConnection().prepareStatement(query);
			ps.setInt(1, ferryId);
			ps.setString(2, direction);
			ResultSet result = ps.executeQuery();
			if(result.next())
				return convertFerryTimeToArray(result);
		}  catch(SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
		
		
	}
	private static Ferry[] convertFerryToArray(ResultSet result) throws SQLException{
		int N = 0;
		result.beforeFirst();
		while(result.next()) N++;
		result.beforeFirst();
		Ferry[] list = new Ferry[N];
		int i=0;
		while(result.next())
			list[i++] = new Ferry(result.getInt("id"), result.getString("name"),result.getString("departure"),result.getString("arrival"));
		return list;

	}
	private static FerryTime[] convertFerryTimeToArray(ResultSet result) throws SQLException{
		int N = 0;
		result.beforeFirst();
		while(result.next()) N++;
		result.beforeFirst();
		FerryTime[] list = new FerryTime[N];
		int i=0;
		while(result.next())
			list[i++] = new FerryTime(result.getInt("time_id"), result.getInt("ferry_id"),result.getString("name"),result.getTime("time"),result.getString("direction"),result.getString("day_code"));
		return list;

	}

}
